
library(here); library(tidyverse); library(lubridate); library(RPostgres); library(fredr)
path <- here::here(); print(path); setwd(path)

fredr_key <- read_file("import_data/input/fred_key.txt"); fredr_set_key(fredr_key)
wrds_user <- read_file("import_data/input/wrds_user.txt")
.wrds_pass <- read_file("import_data/input/wrds_pass.txt")

wrds <- dbConnect(Postgres(),
                  host     = "wrds-pgdata.wharton.upenn.edu",
                  port     = 9737,
                  user     = wrds_user,
                  password = .wrds_pass,
                  dbname   = "wrds")

n_pull <- -1 # n =-1 retrieves all records

res <- dbSendQuery(wrds,"SELECT anndats,actdats,fpedats,fpi,
                ticker,oftic,cusip,measure,estimator,analys,value
                FROM tr_ibes.detu_epsus
                WHERE fpi IN ('6','7','8','9')
                AND anndats < '2023-06-15';")
df_detail_ueps <- dbFetch(res, n = n_pull); dbClearResult(res)

res <- dbSendQuery(wrds, "SELECT actdats,excdats,fpedats,fpi,measure,
                ticker,oftic,cusip,excfla,estimator,analys
                FROM tr_ibes.exc_epsus
                WHERE fpi IN ('6','7','8','9')
                AND excdats < '2023-06-15';")
df_excl_ueps <- dbFetch(res, n = n_pull); dbClearResult(res)

res <- dbSendQuery(wrds, "SELECT anndats,pends,oftic,measure,value
                FROM tr_ibes.actu_epsus
                WHERE measure = 'EPS'
                AND pdicity = 'QTR'
                AND (anndats <= '2023-06-15' OR anndats IS NULL)
                AND (actdats <= '2023-06-15' OR actdats IS NULL)
                AND pends <= '2023-06-15';")
df_actual_ueps <- dbFetch(res, n = n_pull); dbClearResult(res)

res <- dbSendQuery(wrds, "SELECT c.date, c.permno, i.ticker, c.cfacshr
                FROM wrdsapps_link_crsp_ibes.ibcrsphist AS i
                JOIN crsp_a_stock.dsf AS c
                ON i.permno = c.permno
                WHERE i.score <= 1
                AND c.date < '2023-06-15'
                AND c.date BETWEEN i.sdate AND i.edate;")
ibes_crsp_adjust <- dbFetch(res, n = n_pull); dbClearResult(res)

## Saving ----
save(df_detail_ueps,                      file = "import_data/output/ibes_detail.RData")
save(df_excl_ueps,                        file = "import_data/output/ibes_excl.RData")
save(df_actual_ueps,                      file = "import_data/output/ibes_actual.RData")
save(ibes_crsp_adjust,                    file = "import_data/output/ibes_crsp_adjust.RData")

dbDisconnect(wrds)
